package cn.xiuxian.dao.impl;

import cn.xiuxian.dao.IVideoDao;
import cn.xiuxian.entity.User;
import cn.xiuxian.entity.Video;
import cn.xiuxian.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@SuppressWarnings("all")
public class VideoDaoImpl implements IVideoDao {
    @Override
    public List<User> findAllUserByTwoSql() {
        Connection connection = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        List<User>  userList = new ArrayList<>();

        try {
            connection = JdbcUtil.getConnection();
            pst1 = connection.prepareStatement("select  *  from  jdbc_user");
            rs1 = pst1.executeQuery();
            while (rs1.next()){

                Integer userid = rs1.getInt(1);
                String username = rs1.getString(2);
                String password = rs1.getString(3);
                Date birthday = rs1.getDate(4);
                Integer power = rs1.getInt(5);

                User user = new User(userid,username,password,birthday,power);
                List<Video> videoList = new ArrayList<>();
                pst2 = connection.prepareStatement("select *  from jdbc_video where user_id = ?");
                pst2.setInt(1,userid);
                rs2 = pst2.executeQuery();
                while (rs2.next()){
                    Video  video = new Video();
                  Integer id =  rs2.getInt(1);
                  String  title = rs2.getString(2);
                  String  content = rs2.getString(3);
                  Integer uid = rs2.getInt(4);

                  video.setId(id);
                  video.setTitle(title);
                  video.setContent(content);
                  video.setUser(user);

                  videoList.add(video);
                }
                user.setVideoList(videoList);
                userList.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeConnection(null,pst2,rs2);
            JdbcUtil.closeConnection(connection,pst1,rs1);
        }
        return userList;
    }

    @Override
    public List<User> findAllUserByOneSql() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> userList = new ArrayList<>();
        Map<Integer,User> maps= new HashMap<>();

        try {
            connection =JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement("select  u.*, v.id as vid ,v.title,v.content,v.user_id    from jdbc_user u left join jdbc_video v on u.id = v.user_id");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Integer userid = resultSet.getInt(1);

                 if (maps.containsKey(userid)){
                     User user = maps.get(userid);
                     List<Video> videoList = user.getVideoList();
                     String title = resultSet.getString("title");
                     Video video = new Video();
                     if(null != title){

                         Integer vid = resultSet.getInt("vid");
                         String content = resultSet.getString("content");
                         video.setId(vid);
                         video.setTitle(title);
                         video.setContent(content);
                         video.setUser(user);
                         videoList.add(video);
                     }
                     user.setVideoList(videoList);
                     maps.put(userid,user);

                 }else {
                     List<Video> videoList = new ArrayList<>();
                     Integer uid = resultSet.getInt(1);
                     String  username =  resultSet.getString(2);
                     String  password =  resultSet.getString(3);
                     Date    birthday =  resultSet.getDate(4);
                     Integer  power = resultSet.getInt(5);

                     User  user = new User(uid,username,password,birthday,power);

                     String title = resultSet.getString("title");
//                     System.out.println(title);
                     Video video = new Video();
                     if(null != title){

                         Integer vid = resultSet.getInt("vid");
                         String content = resultSet.getString("content");
                         video.setId(vid);
                         video.setTitle(title);
                         video.setContent(content);
                         video.setUser(user);
                         videoList.add(video);
                     }
                     user.setVideoList(videoList);
                     maps.put(userid,user);
                 }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeConnection(connection,preparedStatement,resultSet);
        }
        return new ArrayList<>(maps.values());
    }
}
